ALTER PROC SP_SAVE_UPDATE_TEST_RELEASE_LOT_DETAILS
(
	@RELEASE_LOT_ID INT,
	@RELEASE_ID		INT,
	@LOT_ID		    INT,
	@QTY_AVAILABLE	INT,
	@QTY_PULLED		INT,
	@RELEASE_DATE	DATETIME,
	@WO_REV_NO		VARCHAR(20),
	@LOT_NUMBER		VARCHAR(50),
	@ENTERED_LOT_NO VARCHAR(50),
	@OUT_RELEASE_LOT_ID 	INT OUT,
	@SHIPPED_LOT_NUMBER 	VARCHAR(50) OUT
)
AS
BEGIN
	-- HERE -2 MEAN NEW LOT ENTERED IN RELEASE SCR WHICH IS NOT EXIST IN LOT_TABLE TABLE 
	-- HERE -1 MEAN NEW LOT PULLED INTO RELEASE SCR BUT IT IS EXIST IN LOT_TABLE TABLE 
	DECLARE @REMAIN_QTY INT
	DECLARE @SPLIT_LOT_INDEX INT
	DECLARE @SHIP_LOT_NUMBER VARCHAR(50)
	DECLARE @SHIP_LOT_NO VARCHAR(50)
	DECLARE @GENERATE_LOT_NO VARCHAR(10)

	IF( @RELEASE_LOT_ID = -2 ) 
	BEGIN
		SET @QTY_AVAILABLE = @QTY_PULLED
	END
	IF @ENTERED_LOT_NO IS NULL OR LEN(LTRIM(RTRIM(@ENTERED_LOT_NO))) = 0	
	BEGIN
		SET @ENTERED_LOT_NO = @LOT_NUMBER
	END
		
	SET @REMAIN_QTY = @QTY_AVAILABLE-@QTY_PULLED	
	SET @GENERATE_LOT_NO = 'FALSE'

	SELECT @GENERATE_LOT_NO = PROPERTY_VALUE FROM CONFIGURATION_TABLE WHERE PROPERTY_KEY = 'scalar.chiptrack.operations.release.test.generateLotNo'
	SELECT @OUT_RELEASE_LOT_ID = TRLT_ID FROM TEST_RELEASE_LOT_TABLE WHERE TRLT_ID = @RELEASE_LOT_ID AND TEST_REL_ID = @RELEASE_ID

	IF( @@ROWCOUNT = 0 )
	BEGIN
		IF( @QTY_PULLED > 0 )
		BEGIN
			--WHEN USER ENTER HIS OWN LOT NUMBER ASSAIGN SAME LOTS IF NOT EXISTS ELSE 
			IF @ENTERED_LOT_NO <> @LOT_NUMBER
			BEGIN
				SELECT @SHIP_LOT_NO = SHIPPED_LOT_NUMBER FROM TEST_RELEASE_LOT_TABLE WHERE SHIPPED_LOT_NUMBER = @ENTERED_LOT_NO ORDER BY RELEASE_DATE DESC 
				IF @SHIP_LOT_NO IS NOT NULL
				BEGIN
					SET @SHIP_LOT_NUMBER = @ENTERED_LOT_NO + '_01'
					WHILE( ( SELECT COUNT(*) FROM TEST_RELEASE_LOT_TABLE WHERE SHIPPED_LOT_NUMBER = @SHIP_LOT_NUMBER ) > 0 )
					BEGIN
						SET @SPLIT_LOT_INDEX = SUBSTRING(@SHIP_LOT_NUMBER, LEN(@SHIP_LOT_NUMBER)-1, 2 )

						IF @SPLIT_LOT_INDEX > 10
						BEGIN
							SET @SHIP_LOT_NUMBER = @LOT_NUMBER + '_' + CONVERT(VARCHAR, (@SPLIT_LOT_INDEX + 1))
						END
						ELSE
						BEGIN	
							SET @SHIP_LOT_NUMBER = @LOT_NUMBER + '_0' + CONVERT(VARCHAR, (@SPLIT_LOT_INDEX + 1) )
						END
					END
				END
				ELSE
				BEGIN
					SET @SHIP_LOT_NUMBER = @ENTERED_LOT_NO
				END
				INSERT INTO TEST_RELEASE_LOT_TABLE( TEST_REL_ID, LOT_ID, QTY_AVAILABLE, QTY_PULLED, RELEASE_DATE, WO_REV_NO, TIMESTAMP, LOT_NUMBER, SHIPPED_LOT_NUMBER )
					VALUES( @RELEASE_ID, @LOT_ID, @QTY_AVAILABLE, @QTY_PULLED, CONVERT( VARCHAR, GETDATE(), 101 ), @WO_REV_NO, CURRENT_TIMESTAMP, @LOT_NUMBER,@SHIP_LOT_NUMBER )
				SET @OUT_RELEASE_LOT_ID = @@IDENTITY
				SET @SHIPPED_LOT_NUMBER = @SHIP_LOT_NUMBER
				
				RETURN
			END
			
			IF ( @GENERATE_LOT_NO = 'TRUE' )
			BEGIN
				IF ( @REMAIN_QTY > 0 )
				BEGIN
					SELECT TOP 1 @SHIP_LOT_NO = SHIPPED_LOT_NUMBER FROM TEST_RELEASE_LOT_TABLE WHERE LOT_ID = @LOT_ID ORDER BY RELEASE_DATE DESC 
					IF @SHIP_LOT_NO IS NULL
					BEGIN
						SET @SHIP_LOT_NUMBER = @LOT_NUMBER + '_01'

						WHILE( ( SELECT COUNT(*) FROM TEST_RELEASE_LOT_TABLE WHERE SHIPPED_LOT_NUMBER = @SHIP_LOT_NUMBER ) > 0 )
						BEGIN
							SET @SPLIT_LOT_INDEX = SUBSTRING(@SHIP_LOT_NUMBER, LEN(@SHIP_LOT_NUMBER)-1, 2 )

							IF @SPLIT_LOT_INDEX > 10
							BEGIN
								SET @SHIP_LOT_NUMBER = @LOT_NUMBER + '_' + CONVERT(VARCHAR, (@SPLIT_LOT_INDEX + 1))
							END
							ELSE
							BEGIN	
								SET @SHIP_LOT_NUMBER = @LOT_NUMBER + '_0' + CONVERT(VARCHAR, (@SPLIT_LOT_INDEX + 1) )
							END
						END

						INSERT INTO TEST_RELEASE_LOT_TABLE( TEST_REL_ID, LOT_ID, QTY_AVAILABLE, QTY_PULLED, RELEASE_DATE, WO_REV_NO, TIMESTAMP, LOT_NUMBER, SHIPPED_LOT_NUMBER )
							VALUES( @RELEASE_ID, @LOT_ID, @QTY_AVAILABLE, @QTY_PULLED, CONVERT( VARCHAR, GETDATE(), 101 ), @WO_REV_NO, CURRENT_TIMESTAMP, @LOT_NUMBER,@SHIP_LOT_NUMBER )
						SET @OUT_RELEASE_LOT_ID = @@IDENTITY
						SET @SHIPPED_LOT_NUMBER = @SHIP_LOT_NUMBER
					END
					ELSE
					BEGIN
						IF CHARINDEX( '_', @SHIP_LOT_NO, 0 ) > 0
						BEGIN
							SET @SPLIT_LOT_INDEX = SUBSTRING(@SHIP_LOT_NO, LEN(@SHIP_LOT_NO)-1, 2 )	
						END
						ELSE
						BEGIN
							SET @SPLIT_LOT_INDEX = 0
						END
						
						IF @SPLIT_LOT_INDEX > 10
						BEGIN
							SET @SHIP_LOT_NUMBER = @LOT_NUMBER + '_' + CONVERT(VARCHAR, (@SPLIT_LOT_INDEX + 1))
						END
						ELSE
						BEGIN	
							SET @SHIP_LOT_NUMBER = @LOT_NUMBER + '_0' + CONVERT(VARCHAR, (@SPLIT_LOT_INDEX + 1) )
						END

						WHILE( ( SELECT COUNT(*) FROM TEST_RELEASE_LOT_TABLE WHERE SHIPPED_LOT_NUMBER = @SHIP_LOT_NUMBER ) > 0 )
						BEGIN
							SET @SPLIT_LOT_INDEX = SUBSTRING(@SHIP_LOT_NUMBER, LEN(@SHIP_LOT_NUMBER)-1, 2 )

							IF @SPLIT_LOT_INDEX > 10
							BEGIN
								SET @SHIP_LOT_NUMBER = @LOT_NUMBER + '_' + CONVERT(VARCHAR, (@SPLIT_LOT_INDEX + 1))
							END
							ELSE
							BEGIN	
								SET @SHIP_LOT_NUMBER = @LOT_NUMBER + '_0' + CONVERT(VARCHAR, (@SPLIT_LOT_INDEX + 1) )
							END
						END
						SET @SHIPPED_LOT_NUMBER = @SHIP_LOT_NUMBER
						INSERT INTO TEST_RELEASE_LOT_TABLE( TEST_REL_ID, LOT_ID, QTY_AVAILABLE, QTY_PULLED, RELEASE_DATE, WO_REV_NO, TIMESTAMP, LOT_NUMBER, SHIPPED_LOT_NUMBER )
							VALUES( @RELEASE_ID, @LOT_ID, @QTY_AVAILABLE, @QTY_PULLED, CONVERT( VARCHAR, GETDATE(), 101 ), @WO_REV_NO, CURRENT_TIMESTAMP, @LOT_NUMBER,@SHIP_LOT_NUMBER )
						SET @OUT_RELEASE_LOT_ID = @@IDENTITY
					END	
				END --END OF WHEN REMAIN QTY GREATER BLOCK
				ELSE
				BEGIN
					SELECT TOP 1 @SHIP_LOT_NO = SHIPPED_LOT_NUMBER FROM TEST_RELEASE_LOT_TABLE WHERE LOT_ID = @LOT_ID ORDER BY RELEASE_DATE DESC 
					IF @SHIP_LOT_NO IS NULL
					BEGIN
						SET @SHIP_LOT_NUMBER = @LOT_NUMBER
						
						WHILE( ( SELECT COUNT(*) FROM TEST_RELEASE_LOT_TABLE WHERE SHIPPED_LOT_NUMBER = @SHIP_LOT_NUMBER ) > 0 )
						BEGIN
							IF CHARINDEX( '_', @SHIP_LOT_NUMBER, 0 ) > 0
							BEGIN
								SET @SPLIT_LOT_INDEX = SUBSTRING(@SHIP_LOT_NUMBER, LEN(@SHIP_LOT_NUMBER)-1, 2 )	
							END
							ELSE
							BEGIN
								SET @SPLIT_LOT_INDEX = 0
							END
														
							IF @SPLIT_LOT_INDEX > 10
							BEGIN
								SET @SHIP_LOT_NUMBER = @LOT_NUMBER + '_' + CONVERT(VARCHAR, (@SPLIT_LOT_INDEX + 1))
							END
							ELSE
							BEGIN	
								SET @SHIP_LOT_NUMBER = @LOT_NUMBER + '_0' + CONVERT(VARCHAR, (@SPLIT_LOT_INDEX + 1) )
							END
						END

						INSERT INTO TEST_RELEASE_LOT_TABLE( TEST_REL_ID, LOT_ID, QTY_AVAILABLE, QTY_PULLED, RELEASE_DATE, WO_REV_NO, TIMESTAMP, LOT_NUMBER, SHIPPED_LOT_NUMBER )
							VALUES( @RELEASE_ID, @LOT_ID, @QTY_AVAILABLE, @QTY_PULLED, CONVERT( VARCHAR, GETDATE(), 101 ), @WO_REV_NO, CURRENT_TIMESTAMP, @LOT_NUMBER,@SHIP_LOT_NUMBER )
						SET @OUT_RELEASE_LOT_ID = @@IDENTITY
						SET @SHIPPED_LOT_NUMBER = @SHIP_LOT_NUMBER
					END
					ELSE
					BEGIN
						IF CHARINDEX( '_', @SHIP_LOT_NO, 0 ) > 0
						BEGIN
							SET @SPLIT_LOT_INDEX = SUBSTRING(@SHIP_LOT_NO, LEN(@SHIP_LOT_NO)-1, 2 )	
						END
						ELSE
						BEGIN
							SET @SPLIT_LOT_INDEX = 0
						END

						IF @SPLIT_LOT_INDEX > 10
						BEGIN
							SET @SHIP_LOT_NUMBER = @LOT_NUMBER + '_' + CONVERT(VARCHAR, (@SPLIT_LOT_INDEX + 1))
						END
						ELSE
						BEGIN	
							SET @SHIP_LOT_NUMBER = @LOT_NUMBER + '_0' + CONVERT(VARCHAR, (@SPLIT_LOT_INDEX + 1) )
						END
						
						WHILE( ( SELECT COUNT(*) FROM TEST_RELEASE_LOT_TABLE WHERE SHIPPED_LOT_NUMBER = @SHIP_LOT_NUMBER ) > 0 )
						BEGIN
							SET @SPLIT_LOT_INDEX = SUBSTRING(@SHIP_LOT_NUMBER, LEN(@SHIP_LOT_NUMBER)-1, 2 )

							IF @SPLIT_LOT_INDEX > 10
							BEGIN
								SET @SHIP_LOT_NUMBER = @LOT_NUMBER + '_' + CONVERT(VARCHAR, (@SPLIT_LOT_INDEX + 1))
							END
							ELSE
							BEGIN	
								SET @SHIP_LOT_NUMBER = @LOT_NUMBER + '_0' + CONVERT(VARCHAR, (@SPLIT_LOT_INDEX + 1) )
							END
						END
						SET @SHIPPED_LOT_NUMBER = @SHIP_LOT_NUMBER
						INSERT INTO TEST_RELEASE_LOT_TABLE( TEST_REL_ID, LOT_ID, QTY_AVAILABLE, QTY_PULLED, RELEASE_DATE, WO_REV_NO, TIMESTAMP, LOT_NUMBER, SHIPPED_LOT_NUMBER )
						VALUES( @RELEASE_ID, @LOT_ID, @QTY_AVAILABLE, @QTY_PULLED, CONVERT( VARCHAR, GETDATE(), 101 ), @WO_REV_NO, CURRENT_TIMESTAMP, @LOT_NUMBER, @SHIP_LOT_NUMBER )
						SET @OUT_RELEASE_LOT_ID = @@IDENTITY
					END	
				END
			END -- END OF GENERATE LOT NO IF
			ELSE
			BEGIN
				INSERT INTO TEST_RELEASE_LOT_TABLE( TEST_REL_ID, LOT_ID, QTY_AVAILABLE, QTY_PULLED, RELEASE_DATE, WO_REV_NO, TIMESTAMP, LOT_NUMBER )
					VALUES( @RELEASE_ID, @LOT_ID, @QTY_AVAILABLE, @QTY_PULLED, CONVERT( VARCHAR, GETDATE(), 101 ), @WO_REV_NO, CURRENT_TIMESTAMP, @LOT_NUMBER )
		
				SET @OUT_RELEASE_LOT_ID = @@IDENTITY
				SET @SHIPPED_LOT_NUMBER = @ENTERED_LOT_NO
			END
		END --QTY GREATER
	END --END OF ROW COUNT
	ELSE
	BEGIN
		
		IF( @QTY_PULLED <= 0 )
		BEGIN
			DELETE FROM TEST_RELEASE_LOT_TABLE WHERE TRLT_ID = @RELEASE_LOT_ID
		END

		IF( @QTY_PULLED > 0 )
		BEGIN
			SELECT @SHIP_LOT_NUMBER = SHIPPED_LOT_NUMBER FROM TEST_RELEASE_LOT_TABLE WHERE TRLT_ID = @RELEASE_LOT_ID
			
			IF @ENTERED_LOT_NO <> @SHIP_LOT_NUMBER
			BEGIN
				IF ( @GENERATE_LOT_NO = 'TRUE' )
				BEGIN
					WHILE( ( SELECT COUNT(*) FROM TEST_RELEASE_LOT_TABLE WHERE SHIPPED_LOT_NUMBER = @ENTERED_LOT_NO ) > 0 )
					BEGIN
						IF CHARINDEX( '_', @ENTERED_LOT_NO, 0 ) > 0
						BEGIN
							SET @SPLIT_LOT_INDEX = SUBSTRING(@ENTERED_LOT_NO, LEN(@ENTERED_LOT_NO)-1, 2 )	
						END
						ELSE
						BEGIN
							SET @SPLIT_LOT_INDEX = 0
						END
														
						IF @SPLIT_LOT_INDEX > 10
						BEGIN
							SET @ENTERED_LOT_NO = @ENTERED_LOT_NO + '_' + CONVERT(VARCHAR, (@SPLIT_LOT_INDEX + 1))
						END
						ELSE
						BEGIN	
							SET @ENTERED_LOT_NO = @ENTERED_LOT_NO + '_0' + CONVERT(VARCHAR, (@SPLIT_LOT_INDEX + 1) )
						END
					END	
					SET @SHIPPED_LOT_NUMBER = @ENTERED_LOT_NO
				END
				ELSE
				BEGIN
					SET @SHIPPED_LOT_NUMBER = @ENTERED_LOT_NO
				END
			END
			ELSE
			BEGIN
				SET @SHIPPED_LOT_NUMBER = @ENTERED_LOT_NO
			END
			UPDATE TEST_RELEASE_LOT_TABLE SET
		                QTY_AVAILABLE 	= @QTY_AVAILABLE,
						QTY_PULLED 	    = @QTY_PULLED,
						SHIPPED_LOT_NUMBER = @SHIPPED_LOT_NUMBER,
						TIMESTAMP 	    = CURRENT_TIMESTAMP
			WHERE TRLT_ID = @RELEASE_LOT_ID  AND TEST_REL_ID = @RELEASE_ID

			--SELECT @SHIPPED_LOT_NUMBER = SHIPPED_LOT_NUMBER FROM TEST_RELEASE_LOT_TABLE WHERE TRLT_ID = @RELEASE_LOT_ID
		END		
	END
END
